﻿/******************************************************   
创建者: 王东杰
创建时间: 2017-07-10
功能: 城市物流设置
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using System.Data;
using com.nm.library.Helper;
using com.nm.Helper;
using com.nm.library;
using george.Helper;
using CommodityTransferAPI.Model;

namespace CommodityTransferAPI
{
    [Description("城市物流设置")]
    public class CityLogisticsSettingsHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 保存

        [JsonType]
        [MustLogin]
        [Description("保存")]
        [ReturnType(typeof(CityLogisticsSettings))]
        public ApiResult Save([ParamDescription("输入参数")] CityLogisticsSettingsSave param)
        {
            if (param == null || param.LogisticsDays < 0) return Error("物流天数不能小于0");
            if (param.CheckedList == null || param.CheckedList.Count == 0) return Error("没有检测到要修改的行");
            foreach (var item in param.CheckedList)
            {
                if (string.IsNullOrWhiteSpace(UserInfo.UserExtrSource)) return Error("公司品牌不能为空");
                if (string.IsNullOrWhiteSpace(UserInfo.UserCompanyId)) return Error("公司代码不能为空");
                if (string.IsNullOrWhiteSpace(item.AreaIDBg)) return Error("发货城市不能为空");
                if (string.IsNullOrWhiteSpace(item.AreaIDEnd)) return Error("到货城市不能为空");
            }

            try
            {

                //更新前删除CompanyID和发货城市,到货城市对应的数据
                var list = new List<CommandInfo>();
                int n = 0;
                foreach (var item in param.CheckedList)
                {
                    string deleteSql = $@"
                                    Delete
                                    FROM ngoods.ngoods_Mv_Bas_CityTransDay
                                    where CompanyID='{UserInfo.UserCompanyId}' 
                                    and ExtrSource='{UserInfo.UserExtrSource}' 
                                    and AreaIDBg='{item.AreaIDBg}' 
                                    and AreaIDEnd='{item.AreaIDEnd}'                                                  
                ";
                    //新增数据
                    string insertSql = $@"
                Insert into 
		                ngoods.ngoods_Mv_Bas_CityTransDay(
                        ExtrSource,
                        CompanyID,
                        AreaIDBg,
                        AreaIDEnd,
                        TransDays
                        )
                        Values(
                        '{UserInfo.UserExtrSource}',
                        '{UserInfo.UserCompanyId}',
                        '{item.AreaIDBg}',
                        '{item.AreaIDEnd}',
                        '{param.LogisticsDays}'
                        )     
                ";
                    list.Add(new CommandInfo(deleteSql, null));
                    list.Add(new CommandInfo(insertSql, null));
                }
                n = Helper.ExecuteSqlTran(list);
                if (n > 0)
                {
                    return Success(0);
                }
                else
                {
                    return Error("保存失败!");
                }

            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion 保存

        #region 查询

        #region 获取列表

        [MustLogin]
        [Description("获取列表")]
        [ReturnType(typeof(IList<CityLogisticsSettings>))]
        public ApiResult GetList(
            [ParamDescription("areaid")] string keys = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)
        {
            var count = ReturnCount(keys);
            var result = PaperData<CityLogisticsSettings>.Create(count, Size, Page);
            string strSql = $@"
      select
	ct.CompanyID,
	ct.AreaIDBg,
	ct.AreaIDEnd,
	ct.TransDays,
	area.areaname as ShipCity,
	area2.areaname as ReceivingCity
FROM
	ngoods.ngoods_Mv_Bas_CityTransDay ct left join nods.nods_erp_Bas_Area area on
	ct.AreaIDBg = area.areaid and ct.extrsource = area.extrsource left join nods.nods_erp_Bas_Area area2 on
	ct.AreaIDEnd = area2.areaid  and ct.extrsource = area2.extrsource 
where
ct.extrsource='{UserInfo.UserExtrSource}' and ct.companyid = '{UserInfo.UserCompanyId}'
        {(string.IsNullOrWhiteSpace(keys) ? "" : $" and (ct.AreaIDBg = '{keys}' or  ct.AreaIDEnd = '{keys}') ")}
ORDER by
	ct.areaidbg,ct.AreaIDEnd
        LIMIT {Size} offset {(Page - 1.0) * Size}	
        ";
            var cmd = Helper.GetSqlStringCommand(strSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<CityLogisticsSettings>.ConvertToModel(dt);
            result.Items = list;
            return Success(result);
        }

        #endregion 获取列表               

        #region 返回行数


        public int ReturnCount(
            [ParamDescription("areaid")] string keys = "")
        {
            try
            {
                var countSql = string.Empty;
                #region 查询时获取行数

                countSql = $@"
select
	count(ct.CompanyID)
FROM
	ngoods.ngoods_Mv_Bas_CityTransDay ct left join nods.nods_erp_Bas_Area area on
	ct.AreaIDBg = area.areaid and ct.extrsource = area.extrsource left join nods.nods_erp_Bas_Area area2 on
	ct.AreaIDEnd = area2.areaid  and ct.extrsource = area2.extrsource 
where
ct.extrsource='{UserInfo.UserExtrSource}' and ct.companyid = '{UserInfo.UserCompanyId}'
        {(string.IsNullOrWhiteSpace(keys) ? "" : $" and (ct.AreaIDBg = '{keys}' or  ct.AreaIDEnd = '{keys}') ")}
";
                #endregion 查询时获取行数

                var cmd = Helper.GetSqlStringCommand(countSql);

                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #endregion 查询
    }
}
